<?php
include(dirname(__FILE__)."/../../../Dao/db.php");
    
    function getAllMedia()
    {
        $option = "<option value=\"1\" selected >Google</option>";
        $option .= "<option value=\"2\">Overture</option>";
        return $option;
    }

    function getAllCampaigns()
    {
        //メンバIdを取得
        session_start();
        $memberId = $_SESSION['memberId'];

        //媒体種別を取得
        if(!isset($_POST['search_media_select']))
        {
            $listingType = 1;
        }
        else
        {
            $listingType = $_POST['search_media_select'];
        }
        //メンバId・媒体種別ごとのすべてのキャンペーン
        $query = "SELECT camp_Id,name FROM campaign WHERE ";
        $query .= "memberId=$memberId AND ";
        $query .= "listingType=$listingType";

        $result = mysql_query($query);
        while($row=mysql_fetch_array($result))
        {
            $option .= "<option value=\"{$row['camp_Id']}\">{$row['name']}</option>\r\n";
        }
        return $option;
    }

    function searchAdGroups()
    {
        //メンバIdを取得
        session_start();
        $memberId = $_SESSION['memberId'];

        $listingType = $_POST['search_media_select'];
        $camp_Id = $_POST['search_campaign_select'];
        $adGr_Id = $_POST['search_id'];
        $name = $_POST['search_name'];
        $status = $_POST['search_status'];
        $sync = $_POST['search_sync'];
        $matchType = $_POST['search_type'];

        //キャンペーン名検索
        if($camp_Id != "-1")
        {
            $query = "SELECT name FROM campaign WHERE ";
            $query .= "camp_Id=$camp_Id";
            $result = mysql_query($query);
            while($row=mysql_fetch_array($result))
            {
                $campaignName = $row['name'];
            }
        }
        else
        {
            $campaignName = "-";
        }

        //広告グループ検索
        $query = "SELECT * FROM adGroup WHERE ";
        $query .= "memberId=$memberId AND ";
        $query .= "listingType=$listingType AND ";
        //キャンペーンは「指定なし」以外
        if($camp_Id != "-1")
        {
            $query .= "camp_Id=$camp_Id AND ";
        }
        if(isset($adGr_Id) && $adGr_Id != "")
        {
            $query .= "adGr_Id=$adGr_Id AND ";
        }
        if(isset($name) && $name != "")
        {
            $query .= "name".getMatchCondition($name,$matchType)." AND ";
        }
        if($status != "-1")
        {
            $query .= "status=$status AND ";
        }
        if($sync != "0")
        {
            $query .= "sync=$sync";
        }
        else
        {
            $query .= "(sync=1 OR sync=2 OR sync=3)";
        }
        
        $result = mysql_query($query);
        while($row=mysql_fetch_array($result))
        {
            $trs .= "<tr class=\"tr4\">
                        <td align=\"center\">{$row['adGr_Id']}</td>";
            if($row['sync'] == 2)
            {
                $trs .= "<td align=\"center\"><img src=\"../images/sync_updating.gif\" border=\"0\" title=\"更新待ち\" /></td>";
            }
            else if($row['sync'] == 1)
            {
                $trs .= "<td align=\"center\"><img src=\"../images/sync_on.gif\" border=\"0\" title=\"更新済み\" /></td>";
            }
            else
            {
                $trs .= "<td align=\"center\"><img src=\"../images/sync_error.gif\" border=\"0\" title=\"更新エラー\" /></td>";
            }
            $trs .="<td align=\"left\">$campaignName</td>
                    <td align=\"left\">{$row['name']}</td>
                    <td align=\"left\">{$row['landingUrl']}</td>
                    <td align=\"center\">{$row['maxCpc']}</td>
                    <td align=\"center\"> - </td>
                    <td align=\"center\">{$row['maxContentCpc']}</td>
                    <td align=\"center\">".exchange_status($row['status'])."</td>
                </tr>";
        }
        return $trs;
    }

    function searchCreatives()
    {
        //メンバIdを取得
        session_start();
        $memberId = $_SESSION['memberId'];

        $listingType = $_POST['search_media_select'];
        $camp_Id = $_POST['search_campaign_select'];
        $Crt_Id = $_POST['search_id'];
        $name = $_POST['search_name'];
        $status = $_POST['search_status'];
        $sync = $_POST['search_sync'];
        $matchType = $_POST['search_type'];

        //キャンペーン名検索
        if($camp_Id != "-1")
        {
            $query = "SELECT name FROM campaign WHERE ";
            $query .= "camp_Id=$camp_Id";
            $result = mysql_query($query);
            while($row=mysql_fetch_array($result))
            {
                $campaignName = $row['name'];
            }
        }
        else
        {
            $campaignName = "-";
        }

        //クリエイティブ検索
        $query = "SELECT a.Crt_Id,b.name adGroupName,a.name creativeName,";
        $query .= "a.headLine,a.description1,a.description2,";
        $query .= "a.displayUrl,a.destinationUrl,a.status,a.sync ";
        $query .= "FROM creative a JOIN adGroup b ON a.adGr_Id=b.adGr_Id WHERE ";
        $query .= "a.memberId=$memberId AND ";
        $query .= "a.listingType=$listingType AND ";
        //キャンペーンは「指定なし」以外
        if($camp_Id != "0")
        {
            $query .= "a.camp_Id=$camp_Id AND ";
        }
        if(isset($Crt_Id) && $Crt_Id != "")
        {
            $query .= "a.Crt_Id=$Crt_Id AND ";
        }
        if(isset($name) && $name != "")
        {
            $query .= "a.name".getMatchCondition($name,$matchType)." AND ";
        }
        if($status != "-1")
        {
            $query .= "a.status=$status AND ";
        }
        if($sync != "0")
        {
            $query .= "a.sync=$sync";
        }
        else
        {
            $query .= "(a.sync=1 OR a.sync=2 OR a.sync=3)";
        }

        $result = mysql_query($query);
        while($row=mysql_fetch_array($result))
        {
            $trs .= "<tr class=\"tr3\">
                        <td align=\"center\">{$row['Crt_Id']}</td>";
            if($row['sync'] == 2)
            {
                $trs .= "<td align=\"center\"><img src=\"../images/sync_updating.gif\" border=\"0\" title=\"更新待ち\" /></td>";
            }
            else if($row['sync'] == 1)
            {
                $trs .= "<td align=\"center\"><img src=\"../images/sync_on.gif\" border=\"0\" title=\"更新済み\" /></td>";
            }
            else
            {
                $trs .= "<td align=\"center\"><img src=\"../images/sync_error.gif\" border=\"0\" title=\"更新エラー\" /></td>";
            }
            $trs .="<td align=\"left\">$campaignName</td>
                    <td align=\"left\">{$row['adGroupName']}</td>
                    <td align=\"left\">{$row['creativeName']}</td>
                                    <td align=\"left\"><div class=\"creative_preview\">
                                            <div class=\"creative_preview_title\">{$row['headLine']}</div>
                                            <div class=\"creative_preview_description\">{$row['description1']}<br />
                                            {$row['description2']}</div>
                                            <div class=\"creative_preview_url\">{$row['displayUrl']}</div>
                                    </div></td>
                                    <td align=\"left\">{$row['destinationUrl']}</td>
                                    <td align=\"center\">".exchange_status($row['status'])."</td>
                                </tr>";
        }
        return $trs;
    }

    function getMatchCondition($name,$type)
    {
        if($type == 0)
        {
        return "='$name'";
        }
        else
        {
            return " like binary '%$name%'";
        }
    }

    function exchange_status($status)
    {
        if($status == 0)
        {
            return "Off";
        }
        else if($status == 1)
        {
            return "On";
        }
    }

    ///キーワードプランニング
    function addAllKeywords($keywords)
    {
        session_start();
        $memberId=$_SESSION['memberId'];

        //フォーマットは不正の場合、エラーメッセージが出す
        $keywordArray=checkFormat_keyword($keywords);
        if(!is_array($keywordArray))
        {
            return $keywordArray;
        }
        else
        {
            $duplicate = $_POST['keyword_duplicate'];

            foreach($keywordArray as $keyword)
            {
                $campaignName = $keyword[0];
                $adGroupName = $keyword[1];
                $keywordText = $keyword[2];
                if($keyword[3] == "部分一致")
                {
                $type = 0;
                }
                else
                {
                    $type =1;
                }
                $maxCpc = $keyword[4];
                $destinationUrl = $info[5];
                if($info[6] == "On" || $info[6] == "on")
                {
                    $paused = 0;
                }
                else
                {
                    $paused = 1;
                }

                $query  = "SELECT camp_Id FROM campaign WHERE ";
                $query .= "name='$campaignName' AND ";
                $query .= "status!=9";

                $result = mysql_query($query);
                while($row = mysql_fetch_array($result))
                {
                    $camp_Id = $row['camp_Id'];
                }

                if(!isset($camp_Id))
                {
                    return "キャンペーン名：$campaignName は存在しません。";
                }

                $query = "SELECT adGr_Id FROM adGroup WHERE ";
                $query .= "camp_Id=$camp_Id AND ";
                $query .= "name='$adGroupName' AND ";
                $query .= "status!=9";

                $result = mysql_query($query);
                if($row = mysql_fetch_array($result))
                {
                    $adGr_Id = $row['adGr_Id'];
                }
                else
                {
                    return "広告グループ名：$adGroupName は存在しません。";
                }

                //同一媒体内では許可しない
                if($duplicate == 0)
                {
                    $query = "SELECT * FROM keyword WHERE ";
                    $query .= "memberId=$memberId AND ";
                    $query .= "listingType=1 AND ";
                    $query .= "text='$keywordText' AND ";
                    $query .= "status!=9";

                    $result = mysql_query($query);
                    while($row=mysql_fetch_array($result))
                    {
                        $message = "同一媒体に同じキーワードがある";
                    }
                }
                //同一キャンペーン内では許可しない
                else if($duplicate == 1)
                {
                    $query = "SELECT * FROM keyword WHERE ";
                    $query .= "memberId=$memberId AND ";
                    $query .= "listingType=1 AND ";
                    $query .= "camp_Id=$camp_Id AND ";
                    $query .= "text='$keywordText' AND ";
                    $query .= "status!=9";

                    $result = mysql_query($query);
                    while($row=mysql_fetch_array($result))
                    {
                        $message = "同一キャンペーンに同じキーワードがある";
                    }
                }
                //同一広告グループ内では許可いさい
                else
                {
                    $query = "SELECT * FROM keyword WHERE ";
                    $query .= "memberId=$memberId AND ";
                    $query .= "listingType=1 AND ";
                    $query .= "camp_Id=$camp_Id AND ";
                    $query .= "adGr_Id=$adGr_Id AND ";
                    $query .= "text='$keywordText' AND ";
                    $query .= "status!=9";

                    $result = mysql_query($query);
                    while($row=mysql_fetch_array($result))
                    {
                        $message = "同一広告グループに同じキーワードがある";
                    }
                }

                if(!isset($message))
                {
                    $query = "INSERT INTO keyword (memberId,listingType,camp_Id,adGr_Id,text,";
                    $query .= "type,maxCpc,destinationUrl,paused,sync,registDate,lastUpdate) VALUES(";
                    $query .= "$memberId,";
                    $query .= "1,";
                    $query .= "$camp_Id,";
                    $query .= "$adGr_Id,";
                    $query .= "'$keywordText',";
                    $query .= "$type,";
                    $query .= "$maxCpc,";
                    $query .= "'$destinationUrl',";
                    $query .= "$paused,";
                    //Googleに未同期
                    $query .= "2,now(),now())";
                    $result = mysql_query($query);
                    echo $query;
                    if ($result != "1")
                    {
                        $message = "登録失敗しました。";
                        break;
                    }
                    else
                    {
                        $message = "登録しました。";
                    }
                }
            }
        }
        return $message;
    }

    //入力チェック
    function checkFormat_keyword($keywords)
    {
        $keywordArray = array();

        for($i=0;$i<count($keywords);$i++)
        {
            $info = split(",",rtrim($keywords[$i]));
            if(count($info) != 7)
            {
                $message = "[$i]行目の格式は不正です。カラム数は違います。";
                break;
            }
            else
            {
                //キャンペーン名チェック
                if(strlen($info[0]) == 0)
                {
                    $message = "[$i]行目のキャンペーン名は不正です。";
                    break;
                }

                //広告グループ名
                if(strlen($info[1]) == 0)
                {
                    $message = "[$i]行目の広告グループ名は不正です";
                    break;
                }

                //キーワード
                if(strlen($info[2]) == 0)
                {
                    $message = "[$i]行目のキーワードテキストは不正です";
                    break;
                }

                //マッチタイプ
                if($info[3] != "部分一致" && $info[3] != "全部一致")
                {
                    $message = "[$i]行目のマッチタイプは不正です。「部分一致」あるいは「全部一致」でご入力してください。";
                    break;
                }

                //単価
                if(preg_match("/^[^0-9]{1,}$/",$info[4]))
                {
                    $message = "[$i]行目の単価は半角数字を入力してください。";
                    break;
                }

                //ランディング先URL
                if(strlen($info[5]) == 0)
                {
                    $message = "7[$i]行目のランディング先URLは不正です。";
                    break;
                }

                //ステータス
                if($info[6] != "On" && $info[6] != "Off" && $info[6] != "on" && $info[6] != "off")
                {
                    $message = "8[$i]行目のステータスはon/offを入力してください。";
                    break;
                }

                array_push($keywordArray,$info);
            }
        }

        //return error message. to be improved.
        if(isset($message))
        {
            return $message;
        }
        else
        {
            return $keywordArray;
        }
    }

    ///クリエイティブプランニング
    function addAllCreatives($creatives)
    {
        session_start();
        $memberId=$_SESSION['memberId'];

        //フォーマットは不正の場合、エラーメッセージが出す
        $creativeArray=checkFormat_creative($creatives);
        if(!is_array($creativeArray))
        {
            return $creativeArray;
        }
        else
        {
            foreach($creativeArray as $creative)
            {
                $campaignName = $creative[0];
                $adGroupName = $creative[1];
                $creativeName = $creative[2];
                $headLine = $creative[3];
                $description1 = $creative[4];
                $description2 = $creative[5];
                $displayUrl = $creative[6];
                $destinationUrl = $creative[7];

                if($creative[8] == "On" || $creative[8] == "on")
                {
                    $status = 1;
                }
                else
                {
                    $status = 0;
                }

                $query  = "SELECT camp_Id FROM campaign WHERE ";
                $query .= "name='$campaignName' AND ";
                //未削除されたキャンペーン
                $query .= "status!=9";

                $result = mysql_query($query);
                if($row = mysql_fetch_array($result))
                {
                    $camp_Id = $row['camp_Id'];
                }
                else
                {
                    return "キャンペーン名:$campaignName は存在しません。";
                }

                $query = "SELECT adGr_Id FROM adGroup WHERE ";
                $query .= "camp_Id=$camp_Id AND ";
                $query .= "name='$adGroupName'";

                $result = mysql_query($query);
                if($row = mysql_fetch_array($result))
                {
                    $adGr_Id = $row['adGr_Id'];
                }
                else
                {
                    return "広告グループ名:$adGroupName は存在しません。";
                }

                $query = "INSERT INTO creative (memberId,listingType,camp_Id,adGr_Id,";
                $query .= "name,headLine,description1,description2,";
                $query .= "displayUrl,destinationUrl,status,sync,registDate,lastUpdate) VALUES(";
                $query .= "$memberId,";
                $query .= "1,";
                $query .= "$camp_Id,";
                $query .= "$adGr_Id,";
                $query .= "'$creativeName',";
                $query .= "'$headLine',";
                $query .= "'$description1',";
                $query .= "'$description2',";
                $query .= "'$displayUrl',";
                $query .= "'$destinationUrl',";
                $query .= "$status,";
                //Googleに未同期
                $query .= "2,";
                $query .= "now(),now())";

                $result = mysql_query($query);
                if ($result != "1")
                {
                    $message = "登録失敗しました。";
                    break;
                }
                else
                {
                    $message = "登録しました。";
                }
                
            }
        }
        return $message;
    }

    //入力チェック
    function checkFormat_creative($creatives)
    {
        $creativeArray = array();

        for($i=0;$i<count($creatives);$i++)
        {
            $info = split(",",rtrim($creatives[$i]));
            if(count($info) != 9)
            {
                $message = "[$i]行目の格式は不正です。カラム数は違います。";
                break;
            }
            else
            {
                //キャンペーン名チェック
                if(strlen($info[0]) == 0)
                {
                    $message = "[$i]行目のキャンペーン名は不正です。";
                    break;
                }

                //広告グループ名
                if(strlen($info[1]) == 0)
                {
                    $message = "[$i]行目の広告グループ名は不正です。";
                    break;
                }

                //クリエイティブ名
                if(strlen($info[2]) == 0)
                {
                    $message = "[$i]行目のクリエイティブ名は不正です。";
                    break;
                }

                //タイトル
                if(strlen($info[3]) == 0)
                {
                    $message = "[$i]行目のタイトルは不正です。";
                    break;
                }

                //説明文１
                if(strlen($info[4]) == 0)
                {
                    $message = "[$i]行目の説明文１は不正です。";
                    break;
                }

                //説明文２
                if(strlen($info[5]) == 0)
                {
                    $message = "[$i]行目の説明文２は不正です。";
                    break;
                }

                //表示URL
                if(strlen($info[6]) == 0)
                {
                    $message = "[$i]行目の表示URLは不正です。";
                    break;
                }

                //ランディング先URL
                if(strlen($info[7]) == 0)
                {
                    $message = "[$i]行目のランディング先URLは不正です。";
                    break;
                }

                //ステータス
                if($info[8] != "On" && $info[8] != "Off" && $info[8] != "on" && $info[8] != "off")
                {
                    $message = "[$i]行目の格式は不正です。on/offをご入力してください。";
                    break;
                }

                array_push($creativeArray,$info);
            }
        }

        //return error message. to be improved.
        if(isset($message))
        {
            return $message;
        }
        else
        {
            return $creativeArray;
        }
    }

        ///広告グループプランニング
    function addAllAdGroups($adGroups)
    {
        session_start();
        $memberId=$_SESSION['memberId'];

        //フォーマットは不正の場合、エラーメッセージが出す
        $adGroupArray=checkFormat_adGroup($adGroups);
        if(!is_array($adGroupArray))
        {
            return $adGroupArray;
        }
        else
        {
            $duplicate = $_POST['keyword_duplicate'];

            foreach($adGroupArray as $adgroup)
            {
                $campaignName = $adgroup[0];
                $adGroupName = $adgroup[1];
                $destinationUrl = $adgroup[2];
                $maxCpc = $adgroup[3];
                $contentMatch = $adgroup[4];
                $contentMaxCpc = $adgroup[5];
                if($adgroup[6] == "On" || $adgroup[6] == "on")
                {
                    $status = 1;
                }
                else
                {
                    $status = 0;
                }

                $query  = "SELECT * FROM campaign WHERE ";
                $query .= "name='$campaignName' AND ";
                //未削除されたキャンペーン
                $query .= "status!=9";

                $result = mysql_query($query);
                while($row=mysql_fetch_array($result))
                {
                    $camp_Id = $row['camp_Id'];
                }

                if(!isset($camp_Id))
                {
                    return "キャンペーン：$campaignName が存在しません。";
                }

                if($this->contentMatch == 1)
                {
                    $query = "UPDATE campaign SET enableSeparateContentBids=1 WHERE ";
                    $query .= "memberId=$memberId AND ";
                    $query .= "listingType=1 AND ";
                    $query .= "camp_Id=$camp_Id";

                    $result = mysql_query($query);
                }
                else
                {
                    $query = "UPDATE campaign SET enableSeparateContentBids=0 WHERE ";
                    $query .= "memberId=$memberId AND ";
                    $query .= "listingType=1 AND ";
                    $query .= "camp_Id=$camp_Id";

                    $result = mysql_query($query);
                }

                //まず、同じ広告グループ名チェック
                $query = "SELECT * FROM adGroup WHERE ";
                $query .= "memberId=$memberId AND ";
                $query .= "listingType=1 AND ";
                $query .= "camp_Id=$camp_Id AND ";
                $query .= "name='$adGroupName' AND ";
                $query .= "status!=9";

                $result = mysql_query($query);
                //同じ広告グループ名がある場合、新規できません
                if(mysql_fetch_array($result))
                {
                    $message = "同じ広告グループ名がある";
                }
                else
                {
                    $query = "INSERT INTO adGroup (memberId,listingType,camp_Id,";
                    $query .= "name,landingUrl,maxCpc,maxContentCpc,status,sync) VALUES(";
                    $query .= "$memberId,";
                    $query .= "1,";
                    $query .= "$camp_Id,";
                    $query .= "'$adGroupName',";
                    $query .= "'$destinationUrl',";
                    $query .= "$maxCpc,";
                    $query .= "$contentMaxCpc,";
                    $query .= "$status,";
                    //Googleに未同期
                    $query .= "2)";
                    $result = mysql_query($query);
                    if ($result != "1")
                    {
                        $message = "登録失敗しました。";
                        break;
                    }
                    else
                    {
                        $message = "登録しました。";
                    }
                }

            }
        }
        return $message;
    }

    //入力チェック
    function checkFormat_adGroup($adGroups)
    {
        $adGroupArray = array();

        for($i=0;$i<count($adGroups);$i++)
        {
            $info = split(",",rtrim($adGroups[$i]));
            if(count($info) != 7)
            {
                $message = "[$i]行目の格式は不正です。カラム数は違います。";
                break;
            }
            else
            {
                //キャンペーン名チェック
                if(strlen($info[0]) == 0)
                {
                    $message = "[$i]行目のキャンペーン名は不正です。";
                    break;
                }

                //広告グループ名
                if(strlen($info[1]) == 0)
                {
                    $message = "[$i]行目の広告グループ名は不正です。";
                    break;
                }

                //ランディング先URL
                if(strlen($info[2]) == 0)
                {
                    $message = "[$i]行目のランディング先URLは不正です。";
                    break;
                }

                //単価
                if(preg_match("/^[^0-9]{1,}$/",$info[3]))
                {
                    $message = "[$i]行目の単価は不正です。単価は半角数字をご入力してください。";
                    break;
                }

                //コンテンツマッチ
                if($info[4] != "On" && $info[4] != "Off" && $info[4] != "on" && $info[4] != "off")
                {
                    $message = "[$i]行目の格式は不正です。";
                    break;
                }

                //コンテンツサーチ単価
                if(preg_match("/^[^0-9]{1,}$/",$info[5]))
                {
                    $message = "[$i]行目の格式は不正です";
                    break;
                }

                //ステータス
                if($info[6] != "On" && $info[6] != "Off" && $info[6] != "on" && $info[6] != "off")
                {
                    $message = "[$i]行目の格式は不正です";
                    break;
                }

                array_push($adGroupArray,$info);
            }
        }

        //return error message. to be improved.
        if(isset($message))
        {
            return $message;
        }
        else
        {
            return $adGroupArray;
        }
    }

?>
